<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>mysql 主从搭建 | 失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		<article>
	
		<h1>mysql 主从搭建</h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql 主从 （mysql replication），主要用于 mysql 的实时备份或者读写分离。在配置之前做准备工作，配置两台 mysql 服务器；或者在同一个机器上跑两个 mysql 服务。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql 主从原理非常简单:</p>
<ul>
<li>每个从仅可以设置一个主；</li>
<li>主在执行 sql 之后，记录二进制 log 文件 （bin-log）；</li>
<li>从连接主，并从主获取 binlog ，存于本地 relay-log ，并从上次记住的位置起执行 sql，一旦遇到错误则停止同步。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从这几条 replication 原理来看，可以推论：</p>
<ul>
<li>主从间的数据库不是实时同步，就算网络正常连接，也存在瞬间，主从数据不一致；</li>
<li>如果主从网络断开，从会在网路正常后，批量同步；</li>
<li>如果对从进行修改数据，那么很可能从在执行主的 bin-log 时出现错误而停止同步，这是很危险的操作。所以一般情况下，非常小心的修改从上的数据。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一个衍生的配置是双主，互为主从配置，只要双方不修改冲突，可以工作良好。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以应用在读写分离的场景中，用以降低单台 mysql 服务器的 I/O </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以实现 mysql 服务的 HA 集群。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以是一主多从，也可以是相互主从（主主）。</p>
<h2 id="在同一台机器上配置-MySQL-主从"><a href="#在同一台机器上配置-MySQL-主从" class="headerlink" title="在同一台机器上配置 MySQL 主从"></a>在同一台机器上配置 MySQL 主从</h2><h3 id="安装、配置-mysql（mysql版本5-1-40）"><a href="#安装、配置-mysql（mysql版本5-1-40）" class="headerlink" title="安装、配置 mysql（mysql版本5.1.40）"></a>安装、配置 mysql（mysql版本5.1.40）</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先在同一台机器上配置两个 MySQL 服务（跑两个端口）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># cd /usr/local</span></div><div class="line">[root@192 <span class="built_in">local</span>]<span class="comment"># cp -r mysql mysql_slave</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;拷贝配置文件</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 <span class="built_in">local</span>]<span class="comment"># cd mysql_slave/</span></div><div class="line">[root@192 mysql_slave]<span class="comment"># cp /etc/my.cnf ./my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改配置文件相关参数</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># vim my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;更改 port 为 3307 ， sock 为 /tmp/mysql_slave.sock 以及 datadir </p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/01.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;初始化 mysql_slave</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动准备</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># cp /etc/init.d/mysqld /etc/init.d/mysqldslave</span></div><div class="line">[root@192 mysql_slave]<span class="comment"># vim /etc/init.d/mysqldslave</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/02.png?raw=true" alt=""></figure></p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/32.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改为</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/03.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;最好在前边添加</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/04.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># /etc/init.d/mysqldslave start</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/05.png?raw=true" alt=""></figure></p>
<h3 id="配置主从准备工作"><a href="#配置主从准备工作" class="headerlink" title="配置主从准备工作"></a>配置主从准备工作</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设定 /usr/local/mysql 为主，端口 3306，/usr/local/mysql_slave 为从，端口为 3307 。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录主 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -h127.0.0.1 -P3306</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录从 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -h127.0.0.1 -P3307</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上创建测试数据库</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;创建数据库 db1</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create database db1;</div><div class="line">Query OK, 1 row affected (0.03 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后导出主的 mysql 数据库谈后导入给 db1</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysqldump -S /tmp/mysql.sock mysql &gt; 1.sql</span></div><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql.sock db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<h3 id="配置主（master）"><a href="#配置主（master）" class="headerlink" title="配置主（master）"></a>配置主（master）</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># vim /etc/my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改或添加：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/06.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;两个可选参数（二选一）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">binlog-do-db=db1    <span class="comment">#用来指定需要同步的库，这里只同步 db1</span></div><div class="line">binlog-ignore-db=db1,db2    <span class="comment">#指定忽略不同步的库，这里不同步 db1,db2</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改配置文件后，重启 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># /etc/init.d/mysqld restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设置 root 密码</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysqladmin -uroot -S /tmp/mysql.sock password '123456'</span></div><div class="line">[root@192 ~]<span class="comment"># mysql -uroot -S /tmp/mysql.sock -p123456</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后授权给从一个用来同步数据的用户 repl</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant replication slave on *.* to <span class="string">'repl'</span>@<span class="string">'127.0.0.1'</span> identified by <span class="string">'123123'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只给授权 replication 权限；用户名 repl ；由于是本机，所有用回还地址；密码是123123</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重新刷新权限</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush privileges;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把表的读锁死</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush tables with <span class="built_in">read</span> lock;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要记住前两列的内容，后面会用到</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/07.png?raw=true" alt=""></figure></p>
<h3 id="设置从"><a href="#设置从" class="headerlink" title="设置从"></a>设置从</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># vim /usr/local/mysql_slave/my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改或添加</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/08.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个数值不能和主一样</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可选参数 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">replicate-do-db=db1,db2</div><div class="line">replicate-ignore-db=db1,db2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;意义同主的那两个可选参数，如果主定义过了，那么从上就不用再次加这些参数了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启 从的 mysqld 服务</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># /etc/init.d/mysqldslave restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上创建 db1 数据库，拷贝住的 db1 数据库到从</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock -e "create database db1"</span></div><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录从 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行命令</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave stop;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to master_host=<span class="string">'127.0.0.1'</span>, master_port=3306, master_user=<span class="string">'repl'</span>, master_password=<span class="string">'123123'</span>, master_log_file=<span class="string">'mysql-bin.000001'</span>, master_log_pos=453</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;指定主的 host 127.0.0.1 ；主的 post 3306；读主的用户 repl ，密码 123123；</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave start;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看配置是否成功</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/09.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;slave 两个显示都是 YES 表示成功</p>
<h3 id="测试主从"><a href="#测试主从" class="headerlink" title="测试主从"></a>测试主从</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解锁表的读</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; unlock tables;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上删除一个表</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use db1;</div><div class="line">Database changed</div><div class="line">mysql&gt; show tables;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/10.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;删除表 help_category</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop table help_category;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/11.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;再到从上查看</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/12.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上清空 db1 库的 db 表</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use db1</div><div class="line"></div><div class="line">mysql&gt; select count(*) from db;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/13.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; truncate table db;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/14.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入 slave ，查看 db1 库 db 表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/15.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;得出的结果为 0 了，说明主从是同步的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后在主上删除表 db</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop table db;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上看 db 表不存在了</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/16.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也可以创建一个表</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show create table user\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制这个表，然后创建表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/17.png?raw=true" alt=""></figure></p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/18.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show tables;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看发现多了 表 yanyi</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/19.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从上查看</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/20.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;建议：MySQL 主从机制比较脆弱，谨慎操作、如果重启 master ，务必要先把 slave 停掉，也就是说需要在 slave 上去执行 slave stop 命令，然后再去重启 master 的 mysql 服务，否则很有可能就会中断了，当然重启完成后，还需要把 slave 给开启 slave start 。 </p>
<h2 id="在两台机器上配置-MySQL-主从"><a href="#在两台机器上配置-MySQL-主从" class="headerlink" title="在两台机器上配置 MySQL 主从"></a>在两台机器上配置 MySQL 主从</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;安装、配置 MySQL （mysql 版本5.7.17）</p>
<h3 id="配置主从准备工作-1"><a href="#配置主从准备工作-1" class="headerlink" title="配置主从准备工作"></a>配置主从准备工作</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;两台机器 192.168.0.72 和192.168.0.71  </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设定 192.168.0.72 为主 192.168.0.71 为从</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上创建测试数据库</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># mysql -uroot -p123456</span></div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create database db1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># mysql -uroot -p123456 -e "create database db1;"</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后导出主的 mysql 库数据，然后导入给 db1</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># mysqldump -uroot -p123456 mysql &gt; 1.sql</span></div><div class="line">[root@master ~]<span class="comment"># mysql -uroot -p123456 db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/21.png?raw=true" alt=""></figure></p>
<h3 id="配置主（master）-1"><a href="#配置主（master）-1" class="headerlink" title="配置主（master）"></a>配置主（master）</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># vim /etc/my.cnf</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/22.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改为</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/23.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;两个可选参数（二选一）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">binlog-do-db=db1    <span class="comment">#用来指定需要同步的库，这里只同步 db1</span></div><div class="line">binlog-ignore-db=db1,db2    <span class="comment">#指定忽略不同步的库，这里不同步 db1,db2</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改配置文件后，重启 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># /etc/init.d/mysqld restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后授权给从一个用来同步数据的用户 repl</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant replication slave on *.* to <span class="string">'repl'</span>@<span class="string">'192.168.0.71'</span> identified by <span class="string">'123123'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只给授权 replication 权限；用户名 repl ；授权机器 IP 192.168.0.71；密码是123123</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重新刷新权限</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush privileges;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把表的读锁死</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush tables with <span class="built_in">read</span> lock;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要记住前两列的内容，后面会用到</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/24.png?raw=true" alt=""></figure></p>
<h3 id="设置从（slave）"><a href="#设置从（slave）" class="headerlink" title="设置从（slave）"></a>设置从（slave）</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># vim /etc/my.cnf</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/25.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改为</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/26.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个数值不能和主一样</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可选参数 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">replicate-do-db=db1,db2</div><div class="line">replicate-ignore-db=db1,db2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;意义同主的那两个可选参数，如果主定义过了，那么从上就不用再次加这些参数了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启 从的 mysqld 服务</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># /etc/init.d/mysqld restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上创建 db1 数据库，拷贝主的 db1 数据库到从</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># mysql -uroot -p123456 -e "create database db1;"</span></div><div class="line">[root@slave ~]<span class="comment"># mysql -uroot -p123456 db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录从 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># mysql -uroot -p123456</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行如下命令</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; stop slave;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to master_host=<span class="string">'192.168.0.72'</span>, master_port=3306, master_user=<span class="string">'repl'</span>, master_password=<span class="string">'123123'</span>, master_log_file=<span class="string">'mysql-bin.000001'</span>, master_log_pos=600;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;指定主的 host 192.168.0.72 ；主的 post 3306；读主的用户 repl ，密码 123123；</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从上查看状态</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show slave status\G;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/27.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看是否显示</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只有两个同时 YES ,才算正常。</p>
<h3 id="测试主从-1"><a href="#测试主从-1" class="headerlink" title="测试主从"></a>测试主从</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上清空 db1 库的 db 表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/28.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入 从，查看 db1 库 db 表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/29.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;得出结论为 0 了，说明主从同步的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后在主删除表 db </p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/30.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上查看 db 表不存在了</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/31.png?raw=true" alt=""></figure></p>

	

	
		<span class="different-posts"><a href="/2017/10/12/MySQL/22. mysql主从搭建/" onclick="window.history.go(-1); return false;">⬅️ Go back </a></span>

	

</article>

	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
